﻿using Shared.Method;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace Communication.Shared
{
    public class SQLiteMethod
    {
        /// <summary>
        /// 获取一个SQLitle连接，兼顾创建一个DB
        /// </summary>
        /// <param name="dictoryPath"></param>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public static SQLiteConnection ConnectionSQLite(string dictoryPath, string fileName)
        {
            try
            {
                if (Directory.Exists(dictoryPath) == false)
                {
                    Directory.CreateDirectory(dictoryPath);
                }

                string dbPath = dictoryPath + fileName;

                SQLiteConnection cn = new SQLiteConnection(string.Format("data source=" + dbPath));
                cn.Open();
                cn.Close();
                return cn;
            }
            catch(Exception ex)
            {
                LogHelper.WriteErrLogTxt("ConnectionSQLite", "ERROR", ex.ToString());
                return null;
            }
        }

        /// <summary>
        /// 删除DB
        /// </summary>
        /// <param name="dbPath"></param>
        public static void DeleteDB(string dbPath)
        {
            if (File.Exists(dbPath))
            {
                File.Delete(dbPath);
            }
        }

        /// <summary>
        /// 执行SQL语句,UPDATE,DELETE,INSERT,CREATE TABLE,DROP TABLE
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="sql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static int ExecSQL(SQLiteConnection connection, string sql, SQLiteParameter[] parameters = null)
        {
            try
            {
                if (connection.State != ConnectionState.Open)
                {
                    connection.Open();
                }

                SQLiteCommand cmd = new SQLiteCommand
                {
                    Connection = connection,
                    CommandText = sql
                };

                if (parameters != null)
                {
                    cmd.Parameters.AddRange(parameters);
                }

                int ret = cmd.ExecuteNonQuery();

                cmd.Dispose();

                connection.Close();

                return ret;
            }
            catch(Exception ex)
            {
                LogHelper.WriteErrLogTxt("ExecSQL", sql, ex.ToString());
                return 0;
            }
        }
        /// <summary>
        /// 获取表结构
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static List<string> TitleToList(SQLiteConnection connection, string sql)
        {
            try
            {
                if (connection.State != System.Data.ConnectionState.Open)
                {
                    connection.Open();
                }

                SQLiteCommand cmd = new SQLiteCommand
                {
                    Connection = connection,
                    CommandText = sql
                };

                SQLiteDataReader reader = cmd.ExecuteReader();

                List<string> listCol = new List<string>();

                while (reader.Read())
                {
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        listCol.Add($"{ reader[i]}");
                    }
                }

                reader.Close();
                connection.Close();

                return listCol;
            }
            catch
            {
                return null;
            }
        }

        /// <summary>
        /// 获取表结构
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataTable TitleToTable(SQLiteConnection connection, string sql)
        {
            try
            {
                if (connection.State != System.Data.ConnectionState.Open)
                {
                    connection.Open();
                }

                SQLiteCommand cmd = new SQLiteCommand
                {
                    Connection = connection,
                    CommandText = sql
                };

                SQLiteDataReader reader = cmd.ExecuteReader();

                DataTable dataTable = new DataTable();

                while (reader.Read())
                {
                    dataTable.Columns.Add(new DataColumn($"{ reader[1].ToString()}"));
                }

                DataRow row = dataTable.NewRow();
                for (int i = 0; i < dataTable.Columns.Count; i++)
                {
                    row[i] = dataTable.Columns[i].ColumnName;
                }

                dataTable.Rows.Add(row);

                reader.Close();
                connection.Close();

                return dataTable;
            }
            catch (Exception ex)
            {
                LogHelper.WriteErrLogTxt("ReaderTableColumnsTable", "ERROR", ex.ToString());
                return null;
            }
        }


        /// <summary>
        /// 读取数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="connection"></param>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static List<T> ExecReader<T>(SQLiteConnection connection, string sql, SQLiteParameter[] parameters=null) where T : class, new()
        {
            try
            {
                if (connection.State != ConnectionState.Open)
                {
                    connection.Open();
                }

                SQLiteCommand cmd = new SQLiteCommand
                {
                    Connection = connection,
                    CommandText = sql
                };

                if (parameters != null)
                {
                    cmd.Parameters.AddRange(parameters);
                }


                List<T> ts = new List<T>();

                using (SQLiteDataReader reader = cmd.ExecuteReader())
                {

                    PropertyInfo[] propertyList = typeof(T).GetProperties();

                    while (reader.Read())
                    {
                        T data = new T();
                        for (int i = 0; i < propertyList.Length; i++)
                        {
                            try
                            {
                                if (reader[propertyList[i].Name] == DBNull.Value)
                                {
                                    propertyList[i].SetValue(data, default(T));
                                }
                                else
                                {
                                    propertyList[i].SetValue(data, reader[propertyList[i].Name]);
                                }
                            }
                            catch
                            {
                                propertyList[i].SetValue(data, default(T));
                            }
                        }
                        ts.Add(data);
                    }
                }

                cmd.Dispose();
                connection.Close();

                return ts;
            }
            catch(Exception ex)
            {
                LogHelper.WriteErrLogTxt("ExecReader", sql, ex.ToString());
                return null;
            }
        }

        /// <summary>
        /// 获取第一个数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="connection"></param>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static T GetFirstCell<T>(SQLiteConnection connection, string sql, SQLiteParameter[] parameters=null)
        {
            try
            {
                if (connection.State != ConnectionState.Open)
                {
                    connection.Open();
                }

                SQLiteCommand cmd = new SQLiteCommand
                {
                    Connection = connection,
                    CommandText = sql
                };

                if (parameters != null)
                {
                    cmd.Parameters.AddRange(parameters);
                }

                T cell = default(T);

                var ret = cmd.ExecuteScalar();

                if (ret != DBNull.Value) cell = (T)ret;

                cmd.Dispose();
                connection.Close();

                return cell;
            }
            catch
            {
                return default(T);
            }
        }
        /// <summary>
        /// 写数据的回显
        /// </summary>
        public static event Action<string> OnChange;
        /// <summary>
        /// 写数据到数据库
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="dt"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static bool ReplaceFromDataTable(SQLiteConnection connection, DataTable dt, string tableName)
        {
            try
            {
                if (connection.State != ConnectionState.Open)
                {
                    connection.Open();
                }

                using (var transaction = connection.BeginTransaction())
                {
                    SQLiteCommand cmd = new SQLiteCommand
                    {
                        Connection = connection
                    };

                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        string columns = string.Empty;
                        string values = string.Empty;

                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            columns += string.Format("{0},", dt.Columns[j].ColumnName);

                            if (dt.Rows[i][j] == null)
                            {
                                values += "NULL,";
                            }
                            else
                            {
                                values += (typeof(string) == dt.Columns[j].DataType || typeof(DateTime) == dt.Columns[j].DataType
                                    ? $"'{dt.Rows[i][j]}'" : dt.Rows[i][j]);
                                values += ",";
                            }

                        }

                        string sqlStr = string.Format("REPLACE  INTO {0} ( {1} ) VALUES ( {2} ) ", tableName, columns.TrimEnd(','), values.TrimEnd(','));

                        cmd.CommandText = sqlStr;
                        //cmd.Parameters.AddRange(parameters);

                        var result = cmd.ExecuteNonQuery();

                        if (result >= 1) OnChange?.Invoke($"SUCCESS:{sqlStr}");
                        else OnChange?.Invoke($"FAIL:{sqlStr}");

                    }

                    transaction.Commit();

                    cmd.Dispose();
                }

                connection.Close();

                return true;
            }
            catch (Exception ex)
            {
                LogHelper.WriteErrLogTxt("ReplaceFromDataTable", "ERROR", ex.ToString());
                return false;
            }
        }
    }
}
